﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Exam.Core.DBUtility;

namespace Exam.Core.SQLDAL
{
    public class StudentExamList
    {
        public List<Exam.Core.Model.StudentExamList> getStudentPaperByStuId(int stuId)
        {
            //StudentPaper.StudentID, StudentPaper.PaperStatus, StudentExam.StudentPaper, StudentExam.TotalScore, StudentExam.TotalScorePer, StudentExam.ExamStatus
            string sql = "SELECT * FROM StudentExamList WHERE StudentID=" + stuId;
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            List<Exam.Core.Model.StudentExamList> list = new List<Exam.Core.Model.StudentExamList>();
            foreach (DataRow dr in dt.Rows)
            {
                Exam.Core.Model.StudentExamList obj = new Exam.Core.Model.StudentExamList();
                obj.PaperID = dr["PaperID"].ToString().Trim();
                obj.PaperName = dr["PaperName"].ToString().Trim();
                obj.PaperDesc = dr["PaperDesc"].ToString().Trim();
                obj.PaperTime = dr["PaperTime"].ToString().Trim();
                obj.CreatTime = dr["CreatTime"].ToString().Trim();
                obj.StarTime = dr["StarTime"].ToString().Trim();
                obj.EndTime = dr["EndTime"].ToString().Trim();
                obj.PaperContent = dr["PaperContent"].ToString().Trim();
                obj.PaperType = dr["PaperType"].ToString().Trim();
                obj.CourseId = dr["CourseId"].ToString().Trim();
                obj.DepartmentID = dr["DepartmentID"].ToString().Trim();

                obj.Studentid = dr["Studentid"].ToString().Trim();
                obj.Paperstatus = dr["Paperstatus"].ToString().Trim();
                obj.Studentpaper = dr["Studentpaper"].ToString().Trim();
                obj.Papertotal = dr["PaperTotal"].ToString().Trim();
                obj.Totalscore = dr["Totalscore"].ToString().Trim();
                obj.Totalscoreper = dr["Totalscoreper"].ToString().Trim();
                obj.Examstatus = dr["Examstatus"].ToString().Trim();
                
                list.Add(obj);
            }
            return list;
        }

        public int getSize(string stuId, string starTime, string endTime, string keyword)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" WHERE 1=1 ");
            if (stuId.Trim() != "")
                sb.Append(" AND StudentID=" + stuId + "");
            //if (starTime.Trim() != "")
            //    sb.Append(" AND etime >= '" + starTime + "'");
            //if (endTime.Trim() != "")
            //    sb.Append(" AND etime <= '" + endTime + "'");
            if (keyword.Trim() != "")
                sb.Append(" AND (PaperName like '%" + keyword.Trim() + "%' OR PaperDesc like '%" + keyword.Trim() + "%')");


            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT Count(*)  FROM StudentExamList " + sb.ToString());

            object obj = DbHelperSQL.GetSingle(SQL.ToString());
            return int.Parse(obj.ToString());
        }

        public List<Exam.Core.Model.StudentExamList> getPaperListByPage(string stuId, int pagesize, int page, string starTime, string endTime, string keyword)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" WHERE 1=1 ");
            if (stuId.Trim() != "")
                sb.Append(" AND StudentID=" + stuId + "");
            //if (starTime.Trim() != "")
            //    sb.Append(" AND etime >= '" + starTime + "'");
            //if (endTime.Trim() != "")
            //    sb.Append(" AND etime <= '" + endTime + "'");
            if (keyword.Trim() != "")
                sb.Append(" and (PaperName like '%" + keyword.Trim() + "%' OR PaperDesc like '%" + keyword.Trim() + "%')");

            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT TOP " + pagesize + "  *  FROM StudentExamList ");
            if (page <= 0)
            {
                SQL.Append(sb.ToString());
            }
            else
            {
                SQL.Append(sb.ToString());
                SQL.Append(" AND PaperID NOT IN ( ");
                SQL.Append(" SELECT TOP " + pagesize * page + " PaperID FROM StudentExamList ");
                SQL.Append(sb.ToString() + " ) ");

            }
            sb.Append(" order by PaperID desc");

            DataTable dt = DbHelperSQL.Query(SQL.ToString()).Tables[0];
            List<Exam.Core.Model.StudentExamList> list = new List<Exam.Core.Model.StudentExamList>();
            foreach (DataRow dr in dt.Rows)
            {
                Exam.Core.Model.StudentExamList obj = new Exam.Core.Model.StudentExamList();
                obj.PaperID = dr["PaperID"].ToString().Trim();
                obj.PaperName = dr["PaperName"].ToString().Trim();
                obj.PaperDesc = dr["PaperDesc"].ToString().Trim();
                obj.PaperTime = dr["PaperTime"].ToString().Trim();
                obj.CreatTime = dr["CreatTime"].ToString().Trim();
                obj.StarTime = dr["StarTime"].ToString().Trim();
                obj.EndTime = dr["EndTime"].ToString().Trim();
                obj.PaperContent = dr["PaperContent"].ToString().Trim();
                obj.PaperType = dr["PaperType"].ToString().Trim();
                obj.CourseId = dr["CourseId"].ToString().Trim();
                obj.DepartmentID = dr["DepartmentID"].ToString().Trim();

                obj.Studentid = dr["Studentid"].ToString().Trim();
                obj.Submittime = dr["etime"].ToString().Trim();
                obj.Paperstatus = dr["Paperstatus"].ToString().Trim();
                obj.Studentpaper = dr["Studentpaper"].ToString().Trim();
                obj.Papertotal = dr["PaperTotal"].ToString().Trim();
                obj.Totalscore = dr["Totalscore"].ToString().Trim();
                obj.Totalscoreper = dr["Totalscoreper"].ToString().Trim();
                obj.Examstatus = dr["Examstatus"].ToString().Trim();

                list.Add(obj);
            }
            return list;
        }
        
    }
}
